package com.walkline.util.sqlite;

import java.util.Date;
import java.util.Vector;

import net.rim.device.api.database.Cursor;
import net.rim.device.api.database.DataTypeException;
import net.rim.device.api.database.Database;
import net.rim.device.api.database.DatabaseException;
import net.rim.device.api.database.Row;
import net.rim.device.api.database.Statement;
import net.rim.device.api.util.IntHashtable;

import com.walkline.util.Enumerations.TableName;
import com.walkline.util.Function;

public class SQLManager
{
    private Database _db;

    public SQLManager(Database db) {_db = db;}

    /**
     * 初始化数据库结构并填充必要数据
     */
    public void initStructure()
    {
    	try
        {
    		//创建Version表，用于存储数据库版本信息
            Statement statement = _db.createStatement("CREATE TABLE IF NOT EXISTS Version (ID INTEGER PRIMARY KEY AUTOINCREMENT, ver TEXT)");
            statement.prepare(); statement.execute(); statement.close();

            statement = _db.createStatement("INSERT INTO Version VALUES(null, '0.1')");
            statement.prepare(); statement.execute(); statement.close();

    		//创建CardList表，用于存储信用卡信息
            statement = _db.createStatement("CREATE TABLE IF NOT EXISTS CardList (ID INTEGER PRIMARY KEY AUTOINCREMENT, BankName TEXT, TailNumber TEXT, Accounts DATE, Repayment DATE, PhoneNumber TEXT)");
            statement.prepare(); statement.execute(); statement.close();

            //创建CategoryList表，用于存储消费类型
            statement = _db.createStatement("CREATE TABLE IF NOT EXISTS CategoryList (ID INTEGER PRIMARY KEY AUTOINCREMENT, ShowAsChinese TEXT, ShowAsEnglish TEXT)");
            statement.prepare(); statement.execute(); statement.close();

            statement = _db.createStatement("INSERT INTO CategoryList VALUES(null, '玩具', 'Toy')");
            statement.prepare(); statement.execute(); statement.close();

            statement = _db.createStatement("INSERT INTO CategoryList VALUES(null, '电影', 'Movie')");
            statement.prepare(); statement.execute(); statement.close();

            statement = _db.createStatement("INSERT INTO CategoryList VALUES(null, '晚餐', 'Dinner')");
            statement.prepare(); statement.execute(); statement.close();

            statement = _db.createStatement("INSERT INTO CategoryList VALUES(null, '书籍', 'Books')");
            statement.prepare(); statement.execute(); statement.close();

            //创建CurrencyList表，用于存储货币种类数据
            statement = _db.createStatement("CREATE TABLE IF NOT EXISTS CurrencyList (ID INTEGER PRIMARY KEY AUTOINCREMENT, ShowAsChinese TEXT, ShowAsAbbreviation TEXT, ShowAsSymbol TEXT, DefaultItem INTEGER)");
            statement.prepare(); statement.execute(); statement.close();

            statement = _db.createStatement("INSERT INTO CurrencyList VALUES(null, '人民币','CNY', '¥', 1)");
            statement.prepare(); statement.execute(); statement.close();

            statement = _db.createStatement("INSERT INTO CurrencyList VALUES(null, '美元', 'USD', '$', 0)");
            statement.prepare(); statement.execute(); statement.close();

            //创建RecorderList表，用于存储刷卡记录信息
            statement = _db.createStatement("CREATE TABLE IF NOT EXISTS RecorderList (ID INTEGER PRIMARY KEY AUTOINCREMENT, PaymentDate DATE, Currency INTEGER, Amount DECIMAL(7,2), Category INTEGER, Detail TEXT, UsedCard INTEGER)");
            statement.prepare(); statement.execute(); statement.close();
        } catch(DatabaseException dbe) {Function.errorDialog(dbe.toString());}
    }

    /**
     * 获取指定表的ID列表
     * @param tableName 表名
     * @return 返回ID列表数组 
     */
    public String[] getRecordersID(final String tableName)
    {
    	String[] returnValue = null;
        Vector vector = new Vector();

        try
        {
            Statement statement = _db.createStatement("SELECT id FROM " + tableName);
            statement.prepare();

            Cursor cursor = statement.getCursor();
            Row row;
            int id;

            while(cursor.next())
            {
                row = cursor.getRow();
                id = row.getInteger(0);
                vector.addElement(new Integer(id));
            }
            statement.close();
            cursor.close();
        } catch(DatabaseException dbe) {Function.errorDialog(dbe.toString());} 
          catch(DataTypeException dte) {Function.errorDialog(dte.toString());}

        if (vector.size() > 0)
        {
        	returnValue = new String[vector.size()];

        	for (int i=0; i<vector.size(); i++)
        	{
        		returnValue[i] = vector.elementAt(i).toString();
        	}
        } else {
        	returnValue = new String[1];
        	returnValue[0] = "None";
        }

        return returnValue;
    }

    /**
     * 获取币种记录的ID列表
     * @return 以字符数组形式返回ID列表
     */
    public String[] getCurrenciesID() {return getRecordersID(TableName.CURRENCYLIST);}

    /**
     * 获取分类记录的ID列表
     * @return 以字符数组形式返回ID列表
     */
    public String[] getCategoriesID() {return getRecordersID(TableName.CATEGORYLIST);}

    /**
     * 获取卡片列表记录的ID列表
     * @return  以字符数组形式返回ID列表
     */
    public String[] getCardListsID() {return getRecordersID(TableName.CARDLIST);}

    /**
     * 获取指定ID对应的币种信息
     * @param id 币种ID
     * @return 返回Currency类实例
     */
    public Currency getCurrencyByID(final int id)
    {
    	Currency returnValue = null;

    	try
        {          
            Statement statement = _db.createStatement("SELECT * FROM CurrencyList WHERE id = ?");
            statement.prepare(); statement.bind(1, id);

            Cursor cursor = statement.getCursor();

            while (cursor.next())
            {
                Row row = cursor.getRow();

                int id1 = row.getInteger(0);
                String showAsChinese = row.getString(1);
                String showAsAbbreviation = row.getString(2);
                String showAsSymbol = row.getString(3);
                int defaultItem = row.getInteger(4);

                returnValue = new Currency(id1, showAsChinese, showAsAbbreviation, showAsSymbol, defaultItem);
            }
            statement.close();
            cursor.close();
        } catch(DatabaseException dbe) {Function.errorDialog(dbe.toString());}
    	  catch(DataTypeException dte) {Function.errorDialog(dte.toString());}

    	return returnValue;
    }

    /**
     * 获取指定ID对应的分类信息
     * @param id 币种ID
     * @return 返回Category类实例
     */
    public Category getCategoryByID(final int id)
    {
    	Category returnValue = null;

    	try
        {          
            Statement statement = _db.createStatement("SELECT * FROM CategoryList WHERE id = ?");
            statement.prepare(); statement.bind(1, id);

            Cursor cursor = statement.getCursor();

            while (cursor.next())
            {
                Row row = cursor.getRow();

                int id1 = row.getInteger(0);
                String showAsChinese = row.getString(1);
                String showAsEnglish = row.getString(2);

                returnValue = new Category(id1, showAsChinese, showAsEnglish);
            }
            statement.close();
            cursor.close();
        } catch(DatabaseException dbe) {Function.errorDialog(dbe.toString());}
    	  catch(DataTypeException dte) {Function.errorDialog(dte.toString());}

    	return returnValue;
    }

    /**
     * 获取指定ID对应的卡片信息
     * @param id 币种ID
     * @return 返回Card类实例
     */
    public Card getCardByID(final int id)
    {
    	Card returnValue = null;

    	try
        {          
            Statement statement = _db.createStatement("SELECT * FROM CardList WHERE id = ?");
            statement.prepare(); statement.bind(1, id);

            Cursor cursor = statement.getCursor();

            while (cursor.next())
            {
                Row row = cursor.getRow();

                int id1 = row.getInteger(0);
                String bankName = row.getString(1);
                String tailNumber = row.getString(4);
                long accounts = row.getLong(2);
                long repayment = row.getLong(3);
                String phoneNumber = row.getString(5);

                returnValue = new Card(id1, bankName, tailNumber, accounts, repayment, phoneNumber);
            }
            statement.close();
            cursor.close();
        } catch(DatabaseException dbe) {Function.errorDialog(dbe.toString());}
    	  catch(DataTypeException dte) {Function.errorDialog(dte.toString());}

    	return returnValue;
    }

    Category addCategory(String showAsChinese, String showAsEnglish)
    {        
        Category category = null;

        try
        {
            Statement statement = _db.createStatement("INSERT INTO CategoryList VALUES(null, ?, ?)");
            statement.prepare();
            statement.bind(1, showAsChinese);
            statement.bind(2, showAsEnglish);
            statement.execute();
            statement.close();

            // Query the database for the auto-generated ID of the category just added
            // and create a new Category object.
            statement = _db.createStatement("SELECT id FROM CategoryList WHERE showAsChinese = ? and showAsEnglish = ?");
            statement.prepare();
            statement.bind(1, showAsChinese);
            statement.bind(2, showAsEnglish);

            Cursor cursor = statement.getCursor();
            if (cursor.next())
            {
                Row row = cursor.getRow();
                int id = row.getInteger(0);
                category = new Category(id, showAsChinese, showAsEnglish);                                
            }
            cursor.close();
            statement.close();
        } catch(DatabaseException dbe) {
            Function.errorDialog(dbe.toString());
        } catch(DataTypeException dte) {
        	Function.errorDialog(dte.toString());
        }

        return category;
    }

    int addItem(String name, String location, String phone, int categoryID)
    {    
        long id = -1;
        
        try
        {
            // Insert a new record in the DirectoryItems table          
            Statement statement = _db.createStatement("INSERT INTO DirectoryItems VALUES(null, ?, ?, ?, ?)"); 
            statement.prepare();                        
            statement.bind(1, categoryID);
            statement.bind(2, name);
            statement.bind(3, location);
            statement.bind(4, phone);                    
            statement.execute();  
            statement.close();
        
            // Retrieve the auto-generated ID of the item just added
            id = _db.lastInsertedRowID();            
        }
        catch(DatabaseException dbe)
        {
        	Function.errorDialog(dbe.toString());   
        }         
        
        return (int)id;    
    }

    void updateItem(int id, String name, String location, String phone)
    {
        try
        {          
            // Update the record in the DirectoryItems table for the given id
            Statement statement = _db.createStatement("UPDATE DirectoryItems SET item_name = ?, location = ?, phone = ? WHERE id = ?"); 
            statement.prepare();                
            statement.bind(1, name);
            statement.bind(2, location);
            statement.bind(3, phone);
            statement.bind(4, id);                    
            statement.execute();                                           
            statement.close();
        }
        catch(DatabaseException dbe)
        {
        	Function.errorDialog(dbe.toString());   
        }                
    }

    void deleteCategory(int id)
    {
        try
        {
            // Delete the record in the Category database table
            // corresponding to the highlighted category.
            Statement statement = _db.createStatement("DELETE FROM Category WHERE category_id = ?"); 
            statement.prepare();            
            statement.bind(1, id);
            statement.execute();  
            statement.close();
            
            // Delete all items in the DirectoryItems database
            // table belonging to the highlighted category.            
            statement = _db.createStatement("DELETE FROM DirectoryItems WHERE category_id = ?"); 
            statement.prepare();
            statement.bind(1, id);            
            statement.execute();  
            statement.close();
        }
        catch(DatabaseException dbe)
        {
        	Function.errorDialog(dbe.toString());
        }        
    }    

    void deleteItem(int id)
    {   
        try
        {      
            // Delete the record in the DirectoryItems table for the given id
            Statement statement = _db.createStatement("DELETE FROM DirectoryItems WHERE id = ?");                         
            statement.prepare();            
            statement.bind(1, id);
            statement.execute();  
            statement.close();                
        }
        catch(DatabaseException dbe)
        {
            Function.errorDialog(dbe.toString());
        }        
    }
    

    IntHashtable getCategories1()
    {        
        IntHashtable categories = new IntHashtable();
        try
        {
            // Read in all records from the Category table
            Statement statement = _db.createStatement("SELECT * FROM CategoryList"); 
            statement.prepare();
            Cursor cursor = statement.getCursor();              
            
            Row row;
            int id;
            String showAsChinese;
            String showAsEnglish;
            Category category;                     
            
            // Iterate through the result set.  For each row, create a new
            // Category object and add it to the hash table. 
            while(cursor.next())
            {
                row = cursor.getRow();
                id = row.getInteger(0);
                showAsChinese = row.getString(1);
                showAsEnglish = row.getString(2);
                category = new Category(id, showAsChinese, showAsEnglish);
                categories.put(id, category);                
            }
            statement.close();
            cursor.close();
        } catch(DatabaseException dbe) {
        	Function.errorDialog(dbe.toString());
        } catch(DataTypeException dte) {
        	Function.errorDialog(dte.toString());
        }

        return categories;
    }

    /*
    Vector getItems()
    {
        Vector directoryItems = new Vector();
        
        try
        {          
            // Read in all records from the DirectoryItems table
            Statement statement = _db.createStatement("SELECT * FROM DirectoryItems"); 
            statement.prepare();
            Cursor cursor = statement.getCursor();
            
            // Iterate through the the result set.  For each row, add a
            // new DirectoryItem object to the vector.
            while(cursor.next())
            {                    
                Row row = cursor.getRow();
                
                int id = row.getInteger(0);
                int categoryId = row.getInteger(1);
                String name = row.getString(2);
                String location = row.getString(3);
                String phone = row.getString(4);                                                                 
                
                DirectoryItem item = new DirectoryItem(id, name, location, phone, categoryId);
                directoryItems.addElement(item);                                  
            }
            statement.close();
            cursor.close();
        }
        catch(DatabaseException dbe)
        {
            SQLiteDemo.errorDialog(dbe.toString());
        } 
        catch(DataTypeException dte)
        {
            SQLiteDemo.errorDialog(dte.toString());
        }  
        
        return directoryItems;          
    }  
    */

    /**
     * 关闭当前数据库
     */
    void closeDB()
    {        
        try {            
            _db.close();
        } catch(DatabaseException dbe) {Function.errorDialog(dbe.toString());}
    }
}